using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Threading;

namespace Test.DataCenter.Reports.SSRSReports.ReportModel
{
	/**
	* SSRS report data for the Production tracking system 
	*/
    public class ProdTrackControl
    {

        public static List<ProductionStatus> GetProductionStatus(string startDate, string endDate, string currdate)
        {
            List<ProductionStatus> list = new List<ProductionStatus>();

            SqlConnection adoConn = new SqlConnection();
            SqlCommand adoCmd = null;
            SqlDataReader prodStaus = null;

            IAsyncResult prodResult = null;
            WaitHandle[] waitHandles = new WaitHandle[1];

            adoConn = dbconnection(); 

            adoCmd = new SqlCommand("spProductionStatus", adoConn);
            adoCmd.Parameters.Add(new SqlParameter("@MonthStartDate", startDate));
            adoCmd.Parameters.Add(new SqlParameter("@MonthEndDate", endDate));
            adoCmd.Parameters.Add(new SqlParameter("@CurrDate", currdate));
            adoCmd.CommandType = CommandType.StoredProcedure;

            try
            {
                adoConn.Open();

                prodResult = adoCmd.BeginExecuteReader();

                waitHandles[0] = prodResult.AsyncWaitHandle;
                WaitHandle.WaitAll(waitHandles);

                prodStaus = adoCmd.EndExecuteReader(prodResult);

                int y =int.Parse(currdate.Split('/')[1]);

                int day= (8<y)? y: 8;

                // adding production days
                for (int x = 1; x <= day; x++)
                {
                    ProductionStatus p = new ProductionStatus();
                    p.ProdDay = x;
                    p.ShippedCount = 0;
                    p.ReadyToProTMS = 0;
                    p.ReadyToProLTS = 0;
                    list.Add(p);
                }

                while (prodStaus.Read())
                {
                   
                    int x = prodStaus.GetOrdinal("ProdDay");
                    if (!prodStaus.IsDBNull(x)){
                    
                        int pday = (int)prodStaus.GetInt32(x);
                        ProductionStatus p = list[pday - 1];

                        int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                        if (t == 1)
                        {
                            p.ShippedCount = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                        }

                        if (t == 2)
                        {
                            int app = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("FkApplicationID"));

                            switch(app){

                                case 3: 
                                    p.ReadyToProTMS = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                    break;

                                case 4:
                                    p.ReadyToProLTS = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                    break;

                            } 
                        }
                   }

                }

                adoConn.Close();
            }
            catch (Exception e)
            {

            }

            return list;
        }


        public static List<BacklogInstitute> GetBacklogInstitute(string startDate, string endDate, string currdate)
        {
            List<BacklogInstitute> list = new List<BacklogInstitute>();

            try
            {
                int y = int.Parse(currdate.Split('/')[1]);

                int day = (8 < y) ? y : 8;

                for (int x = 1; x <= day; x++)
                {
                    BacklogInstitute p = new BacklogInstitute();
                    p.ProdDay = x;
                    p.NoOfInstitute = 0;
                    list.Add(p);
                }

                List<ProductionStatus> l = GetProductionStatus(startDate, endDate, currdate);

                for (int i = 0; i < day; i++)
                {
                    ProductionStatus s = l[i];
                    BacklogInstitute p = list[i];
                    p.NoOfInstitute = s.ReadyToProTMS- s.ShippedCount;
                }
              
            }
            catch (Exception ex)
            {

            }

            return list;
        }


        public static List<AvgBacklogPerDay> GetAvgBacklogPerDay(string startDate, string endDate,string currdate)
        {
            List<AvgBacklogPerDay> list = new List<AvgBacklogPerDay>();
            int sumbacklog = 0;

            try
            {

                int y = int.Parse(currdate.Split('/')[1]);

                int day = (8 < y) ? y : 8;

                for (int x = 1; x <= day; x++)
                {
                    AvgBacklogPerDay p = new AvgBacklogPerDay();
                    p.ProdDay = x;
                    list.Add(p);
                }

                List<ProductionStatus> l = GetProductionStatus(startDate, endDate, currdate);

                for (int i = 0; i < day; i++)
                {
                    ProductionStatus s = l[i];
                    AvgBacklogPerDay p = list[i];
                    sumbacklog = sumbacklog + (s.ReadyToProTMS - s.ShippedCount);
                    p.AvgBacklog = sumbacklog / (i + 1);

                }

            }
            catch (Exception ex)
            {

            }

            return list;
        }


        public static List<AvgBacklogByMonth> GetAvgBacklogByMonth(string backlogYear)
        {
            
            List<AvgBacklogByMonth> list = new List<AvgBacklogByMonth>();
            int[] totalbacklog = new int[12];
            int ready = 0;
            int ship = 0;

            string startDate = "01/01/" + backlogYear;
            string endDate = "12/31/" + backlogYear;
            string[] mons = { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" };

            SqlConnection adoConn = new SqlConnection();
            SqlCommand adoCmd = null;
            SqlDataReader prodStaus = null;

            IAsyncResult prodResult = null;
            WaitHandle[] waitHandles = new WaitHandle[1];

            adoConn = dbconnection();

            adoCmd = new SqlCommand("spBacklogForYear", adoConn);
            adoCmd.Parameters.Add(new SqlParameter("@YearStartDate", startDate));
            adoCmd.Parameters.Add(new SqlParameter("@YearEndDate", endDate));
            adoCmd.CommandType = CommandType.StoredProcedure;


            try
            {
                adoConn.Open();

                prodResult = adoCmd.BeginExecuteReader();

                waitHandles[0] = prodResult.AsyncWaitHandle;
                WaitHandle.WaitAll(waitHandles);

                prodStaus = adoCmd.EndExecuteReader(prodResult);

                while (prodStaus.Read())
                {

                    int x = prodStaus.GetOrdinal("Months");
                    if (!prodStaus.IsDBNull(x))
                    {
                        int months = (int)prodStaus.GetInt32(x);

                        switch(months){
                            case 1:
                                  int y1 = prodStaus.GetOrdinal("ProdDay");
                                  if (!prodStaus.IsDBNull(y1))
                                  {
                                      int pday = (int)prodStaus.GetInt32(y1);
                                      if (pday <= 10)
                                      {
                                          int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                          if (t == 1)
                                          {
                                              ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }
                                          if (t == 2)
                                          {
                                              ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }

                                          totalbacklog[0] = totalbacklog[0] + (ready - ship);
                                          ready = ship = 0;
                                      }
                                  }

                                break;
                            case 2:
                                  int y2 = prodStaus.GetOrdinal("ProdDay");
                                  if (!prodStaus.IsDBNull(y2))
                                  {
                                      int pday = (int)prodStaus.GetInt32(y2);
                                      if (pday <= 10)
                                      {
                                          int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                          if (t == 1)
                                          {
                                              ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }
                                          if (t == 2)
                                          {
                                              ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }

                                          totalbacklog[1] = totalbacklog[1] + (ready - ship);
                                          ready = ship = 0;
                                      }
                                  }

                                break;

                            case 3:
                                 int y3= prodStaus.GetOrdinal("ProdDay");
                                  if (!prodStaus.IsDBNull(y3))
                                  {
                                      int pday = (int)prodStaus.GetInt32(y3);
                                      if (pday <= 10)
                                      {
                                          int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                          if (t == 1)
                                          {
                                              ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }
                                          if (t == 2)
                                          {
                                              ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }

                                          totalbacklog[2] = totalbacklog[2] + (ready - ship);
                                          ready = ship = 0;
                                      }
                                  }

                                break;

                            case 4:

                                 int y4 = prodStaus.GetOrdinal("ProdDay");
                                  if (!prodStaus.IsDBNull(y4))
                                  {
                                      int pday = (int)prodStaus.GetInt32(y4);
                                      if (pday <= 10)
                                      {
                                          int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                          if (t == 1)
                                          {
                                              ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }
                                          if (t == 2)
                                          {
                                              ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }

                                          totalbacklog[3] = totalbacklog[3] + (ready - ship);
                                          ready = ship = 0;
                                      }
                                  }
                                break;

                            case 5:
                                 int y5 = prodStaus.GetOrdinal("ProdDay");
                                  if (!prodStaus.IsDBNull(y5))
                                  {
                                      int pday = (int)prodStaus.GetInt32(y5);
                                      if (pday <= 10)
                                      {
                                          int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                          if (t == 1)
                                          {
                                              ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }
                                          if (t == 2)
                                          {
                                              ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }

                                          totalbacklog[4] = totalbacklog[4] + (ready - ship);
                                          ready = ship = 0;
                                      }
                                  }

                                break;

                            case 6:
                                 int y6 = prodStaus.GetOrdinal("ProdDay");
                                  if (!prodStaus.IsDBNull(y6))
                                  {
                                      int pday = (int)prodStaus.GetInt32(y6);
                                      if (pday <= 10)
                                      {
                                          int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                          if (t == 1)
                                          {
                                              ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }
                                          if (t == 2)
                                          {
                                              ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }

                                          totalbacklog[5] = totalbacklog[5] + (ready - ship);
                                          ready = ship = 0;
                                      }
                                  }

                                break;

                            case 7:
                                 int y7 = prodStaus.GetOrdinal("ProdDay");
                                  if (!prodStaus.IsDBNull(y7))
                                  {
                                      int pday = (int)prodStaus.GetInt32(y7);
                                      if (pday <= 10)
                                      {
                                          int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                          if (t == 1)
                                          {
                                              ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }
                                          if (t == 2)
                                          {
                                              ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }

                                          totalbacklog[6] = totalbacklog[6] + (ready - ship);
                                          ready = ship = 0;
                                      }
                                  }

                                break;

                            case 8:
                                 int y8= prodStaus.GetOrdinal("ProdDay");
                                 if (!prodStaus.IsDBNull(y8))
                                  {
                                      int pday = (int)prodStaus.GetInt32(y8);
                                      if (pday <= 10)
                                      {
                                          int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                          if (t == 1)
                                          {
                                              ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }
                                          if (t == 2)
                                          {
                                              ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }

                                          totalbacklog[7] = totalbacklog[7] + (ready - ship);
                                          ready = ship = 0;
                                      }
                                  }

                                break;

                            case 9:
                                 int y9 = prodStaus.GetOrdinal("ProdDay");
                                  if (!prodStaus.IsDBNull(y9))
                                  {
                                      int pday = (int)prodStaus.GetInt32(y9);
                                      if (pday <= 10)
                                      {
                                          int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                          if (t == 1)
                                          {
                                              ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }
                                          if (t == 2)
                                          {
                                              ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }

                                          totalbacklog[1] = totalbacklog[1] + (ready - ship);
                                          ready = ship = 0;
                                      }
                                  }

                                break;

                            case 10:

                                 int y10 = prodStaus.GetOrdinal("ProdDay");
                                  if (!prodStaus.IsDBNull(y10))
                                  {
                                      int pday = (int)prodStaus.GetInt32(y10);
                                      if (pday <= 10)
                                      {
                                          int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                          if (t == 1)
                                          {
                                              ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }
                                          if (t == 2)
                                          {
                                              ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }

                                          totalbacklog[9] = totalbacklog[9] + (ready - ship);
                                          ready = ship = 0;
                                      }
                                  }
                                break;

                            case 11:

                                 int y11 = prodStaus.GetOrdinal("ProdDay");
                                  if (!prodStaus.IsDBNull(y11))
                                  {
                                      int pday = (int)prodStaus.GetInt32(y11);
                                      if (pday <= 10)
                                      {
                                          int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                          if (t == 1)
                                          {
                                              ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }
                                          if (t == 2)
                                          {
                                              ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }

                                          totalbacklog[10] = totalbacklog[10] + (ready - ship);
                                          ready = ship = 0;
                                      }
                                  }
                                break;

                            case 12:
                                 int y12 = prodStaus.GetOrdinal("ProdDay");
                                  if (!prodStaus.IsDBNull(y12))
                                  {
                                      int pday = (int)prodStaus.GetInt32(y12);
                                      if (pday <= 10)
                                      {
                                          int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                          if (t == 1)
                                          {
                                              ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }
                                          if (t == 2)
                                          {
                                              ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                          }

                                          totalbacklog[11] = totalbacklog[11] + (ready - ship);
                                          ready = ship = 0;
                                      }
                                  }

                                break;
                        }

                    }


                }

                for (int z = 0; z < 12; z++)
                {
                    AvgBacklogByMonth l = new AvgBacklogByMonth();
                    l.Monthly = mons[z];
                    l.BacklogAmt = totalbacklog[z] / 10;
                    list.Add(l);
                }

                adoConn.Close();
            }
            catch (Exception e)
            {

            }
                   
            return list;

        }


        public static List<ShippedDay> GetShippedByDay(string startDate, string endDate){


            List<ShippedDay> list = new List<ShippedDay>();

            SqlConnection adoConn = new SqlConnection();
            SqlCommand adoCmd = null;
            SqlDataReader prodStaus = null;

            IAsyncResult prodResult = null;
            WaitHandle[] waitHandles = new WaitHandle[1];

            adoConn = dbconnection();

            adoCmd = new SqlCommand("spClientReportShippedByDay", adoConn);
            adoCmd.Parameters.Add(new SqlParameter("@MonthStartDate", startDate));
            adoCmd.Parameters.Add(new SqlParameter("@MonthEndDate", endDate));
            adoCmd.CommandType = CommandType.StoredProcedure;

            try
            {
                adoConn.Open();

                prodResult = adoCmd.BeginExecuteReader();

                waitHandles[0] = prodResult.AsyncWaitHandle;
                WaitHandle.WaitAll(waitHandles);

                prodStaus = adoCmd.EndExecuteReader(prodResult);

                int y = int.Parse(endDate.Split('/')[1]);

                int day = (10 < y) ? y : 10;

                for (int x = 1; x <= day; x++)
                {
                    ShippedDay p = new ShippedDay();
                    p.ProdDay = x;
                    list.Add(p);
                }

                while (prodStaus.Read())
                {
                    int pday= (int)prodStaus.GetInt32(prodStaus.GetOrdinal("ProdDay"));
                    ShippedDay p = list[pday - 1];

                    p.ShippedNum = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("ShippedCount"));
                    list.Add(p);
                }

                adoConn.Close();
            }
            catch (Exception e)
            {

            }

            return list;
        }


        public static List<ProcessTurnaround> GetProcessTurnaround(string startDate, string endDate)
        {
            List<ProcessTurnaround> list = new List<ProcessTurnaround>();

            SqlConnection adoConn = new SqlConnection();
            SqlCommand adoCmd = null;
            SqlDataReader prodStaus = null;

            IAsyncResult prodResult = null;
            WaitHandle[] waitHandles = new WaitHandle[1];

            adoConn = dbconnection();

            adoCmd = new SqlCommand("spProcessingTurnaround", adoConn);
            adoCmd.Parameters.Add(new SqlParameter("@MonthStartDate", startDate));
            adoCmd.Parameters.Add(new SqlParameter("@MonthEndDate", endDate));
            adoCmd.CommandType = CommandType.StoredProcedure;


            try
            {
                adoConn.Open();

                prodResult = adoCmd.BeginExecuteReader();

                waitHandles[0] = prodResult.AsyncWaitHandle;
                WaitHandle.WaitAll(waitHandles);

                prodStaus = adoCmd.EndExecuteReader(prodResult);

                int y =int.Parse(endDate.Split('/')[1]);

                int day= (8<y)? y: 8;

                // adding production days
                for (int x = 0 ; x <= day; x++)
                {
                    ProcessTurnaround temp = new ProcessTurnaround();
                    temp.ProdDay = x;
                    temp.NumOfClient = 0;
                    list.Add(temp);

                }

                    while (prodStaus.Read())
                    {
                        int x = prodStaus.GetOrdinal("Shipped");
                        int re = prodStaus.GetOrdinal("ReadyProcess");

                        if (!prodStaus.IsDBNull(x) )
                        {
                            if (!prodStaus.IsDBNull(re))
                            {

                                int ship = (int)prodStaus.GetInt32(x);
                                int ready = (int)prodStaus.GetInt32(re);


                                ProcessTurnaround temp = list[ship - ready];
                                temp.NumOfClient++;
                            }
                        }
                    }

            }
            catch (Exception ex)
            {

            }

            return list;
        }


        public static List<ProductionException> GetProductionException(string startDate, string endDate,string currdate)
        {
            List<ProductionException> list = new List<ProductionException>();
            int readyTMSTotal = 0;
             int readyLTSTotal = 0;

            int numofTMSOnhold = 0;
            int numofLTSOnhold = 0;
            int notTMSprocess = 0;
            int notLTSprocess = 0;

            SqlConnection adoConn = new SqlConnection();
            SqlCommand adoCmd = null;
            SqlDataReader prodStaus = null;

            SqlCommand adoHoldCmd = null;
            SqlDataReader onholdData = null;

            SqlCommand adoNProCmd = null;
            SqlDataReader nProData = null;


            IAsyncResult prodResult = null;
            WaitHandle[] waitHandles = new WaitHandle[1];

            //int totalActClient = getNumOfActiveClients();
            int totalActClient = 20;
            string[] pDate = startDate.Split('/');
            string productionDay = pDate[0] + "/01/" + pDate[2];

            adoConn = dbconnection();

            adoCmd = new SqlCommand("spDailyCumulativeClient", adoConn);
            adoCmd.Parameters.Add(new SqlParameter("@MonthStartDate", startDate));
            adoCmd.Parameters.Add(new SqlParameter("@MonthEndDate", endDate));
            adoCmd.Parameters.Add(new SqlParameter("@CurrDate", currdate));
            adoCmd.CommandType = CommandType.StoredProcedure;

            try
            {
                adoConn.Open();

                prodResult = adoCmd.BeginExecuteReader();

                waitHandles[0] = prodResult.AsyncWaitHandle;
                WaitHandle.WaitAll(waitHandles);

                prodStaus = adoCmd.EndExecuteReader(prodResult);

                while (prodStaus.Read())
                {
                    int app = prodStaus.GetOrdinal("FkApplicationID");
                    if (!prodStaus.IsDBNull(app))
                    {
                        int appID = prodStaus.GetInt32(app);

                        switch (appID)
                        {

                            case 3:
                                int x = prodStaus.GetOrdinal("ProdDay");

                                if (!prodStaus.IsDBNull(x))
                                {
                                    int readytotms = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("ReadyToProcess"));
                                    readyTMSTotal = readyTMSTotal + readytotms;
                                }

                                break;

                            case 4:
                                int z = prodStaus.GetOrdinal("ProdDay");

                                if (!prodStaus.IsDBNull(z))
                                {
                                    int readytolts = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("ReadyToProcess"));
                                    readyLTSTotal = readyLTSTotal + readytolts;
                                }


                                break;
                        }

                    }

                }

                string sqlHold = "SELECT count(P.HasErrors) AS OnHold, FkApplicationID " +
                                 "FROM ProductionStatus2 AS P " +
                                 "WHERE (P.FkApplicationID =3 OR P.FkApplicationID =4) AND P.AreAllFilesUploaded =1 AND P.ProcessedDate =" + currdate + "AND P.HasErrors =1 " +
                                 "GROUP BY P.HasErrors, FkApplicationID";

                adoHoldCmd = adoConn.CreateCommand();
                adoHoldCmd.CommandText = sqlHold;
                adoHoldCmd.CommandType = CommandType.Text;

                onholdData = adoHoldCmd.ExecuteReader();

                while (onholdData.Read())
                {
                    int app = onholdData.GetOrdinal("FkApplicationID");

                    if (!onholdData.IsDBNull(app))
                    {
                        int appID = onholdData.GetInt32(app);

                        switch (appID)
                        {

                            case 3:
                                if (!onholdData.IsDBNull(onholdData.GetOrdinal("OnHold")))
                                {
                                    numofTMSOnhold = (int)onholdData.GetInt32(onholdData.GetOrdinal("OnHold"));
                                }
                              break;


                            case 4:
                                  if (!onholdData.IsDBNull(onholdData.GetOrdinal("OnHold")))
                                  {
                                     numofLTSOnhold = (int)onholdData.GetInt32(onholdData.GetOrdinal("OnHold"));
                                  }
                              break;
                        }


                    }
                }

                string nProSQl = "SELECT COUNT(*) AS NotProcess,  FkApplicationID FROM ProductionStatus2 As P " +
                                "WHERE P.IsProcessed='0' AND P.ProductionDate =" + startDate +
                                "Group By P.ProcessedDate, P.FkApplicationID";

                adoNProCmd = adoConn.CreateCommand();
                adoNProCmd.CommandText = nProSQl;
                adoNProCmd.CommandType = CommandType.Text;

                nProData = adoNProCmd.ExecuteReader();

                while (nProData.Read())
                {
                    int app = nProData.GetOrdinal("FkApplicationID");

                    if (!nProData.IsDBNull(app))
                    {
                        int appID = nProData.GetInt32(app);

                        switch (appID)
                        {

                            case 3:
                                if (!nProData.IsDBNull(nProData.GetOrdinal("NotProcess")))
                                {
                                    notTMSprocess = (int)nProData.GetInt32(nProData.GetOrdinal("NotProcess"));
                                }
                                break;


                            case 4:
                                if (!nProData.IsDBNull(nProData.GetOrdinal("NotProcess")))
                                {
                                    notLTSprocess = (int)nProData.GetInt32(nProData.GetOrdinal("NotProcess"));
                                }
                                break;
                        }


                    }
                }


                ProductionException wait = new ProductionException();
                wait.Descrip = "Waiting for Data";
                wait.ProcessTMSVal = (totalActClient - readyTMSTotal) - numofTMSOnhold - notTMSprocess;
                wait.ProcessLTSVal = (totalActClient - readyLTSTotal) - numofLTSOnhold - notLTSprocess;
                list.Add(wait);

                ProductionException onHd = new ProductionException();
                onHd.Descrip = "On Hold";
                onHd.ProcessTMSVal = numofTMSOnhold;
                onHd.ProcessLTSVal = numofLTSOnhold;
                list.Add(onHd);

                ProductionException notPro = new ProductionException();
                notPro.Descrip = "Not Processing";
                notPro.ProcessTMSVal = notTMSprocess;
                notPro.ProcessLTSVal = notLTSprocess;
                list.Add(notPro);

            }
            catch(Exception ex)
            {

            }


            return list;
        }


        public static List<ReportApprovedFirstReview> GetReportApprovedFirstReview(string startDate, string endDate)
        {
            List<ReportApprovedFirstReview> list = new List<ReportApprovedFirstReview>();
            int totalClient=0;
                      
            SqlConnection adoConn = new SqlConnection();
            SqlCommand adoCmd = null;
            SqlDataReader appClient = null;

            IAsyncResult approvedFirst = null;
            WaitHandle[] waitHandles = new WaitHandle[1];

            DateTime stdate = Convert.ToDateTime(startDate);
            DateTime eddate = Convert.ToDateTime(endDate);
            int mondif = ((eddate.Year - stdate.Year) * 12) + eddate.Month - stdate.Month;


            for (int y = 0; y <= mondif; y++)
            {
                ReportApprovedFirstReview temp = new ReportApprovedFirstReview();

                DateTime t= stdate.AddMonths(y);

                temp.Monthlydate = t.Year.ToString().Substring(2, 2) + " " + getMonthStr(t.Month - 1);

                list.Add(temp);

            }


            adoConn = dbconnection();

            adoCmd = new SqlCommand("spReportApprovedFirstReview", adoConn);
            adoCmd.Parameters.Add(new SqlParameter("@StartProdDate", startDate));
            adoCmd.Parameters.Add(new SqlParameter("@EndProdDate", endDate));
            adoCmd.CommandType = CommandType.StoredProcedure;

            try
            {
                // totalClient = getNumOfActiveClients();
                totalClient = 20;

                adoConn.Open();

                approvedFirst = adoCmd.BeginExecuteReader();

                waitHandles[0] = approvedFirst.AsyncWaitHandle;
                WaitHandle.WaitAll(waitHandles);

                appClient = adoCmd.EndExecuteReader(approvedFirst);

                while (appClient.Read())
                {
                   // ReportApprovedFirstReview temp = list[]
                    DateTime t=appClient.GetDateTime(appClient.GetOrdinal("ProductionDate"));
                    int d = Math.Abs(((stdate.Year - t.Year) * 12) + stdate.Month - t.Month);

                    ReportApprovedFirstReview rp = list[d];
                    double appr = appClient.GetInt32(appClient.GetOrdinal("Approved")); 
                    double val =   (appr/ totalClient) * 100;
                    rp.ApprovedVal = (int) val;

                 }

            }
            catch (Exception ex)
            {

            }

            return list;
        }


        public static List<ErrorRepotedByClient> GetErrorRepotedByClient(string startDate, string endDate)
        {

            List<ErrorRepotedByClient> list = new List<ErrorRepotedByClient>();

            SqlConnection adoConn = new SqlConnection();
            SqlCommand adoCmd = null;
            SqlDataReader errData = null;

            IAsyncResult prodResult = null;
            WaitHandle[] waitHandles = new WaitHandle[1];

            int totalClient = 0;
            totalClient = 20;
           //totalClient = getNumOfActiveClients();


            DateTime stdate = Convert.ToDateTime(startDate);
            DateTime eddate = Convert.ToDateTime(endDate);
            int mondif = ((eddate.Year - stdate.Year) * 12) + eddate.Month - stdate.Month;


           

            for (int y = 0; y <= mondif; y++)
            {
                DateTime t = stdate.AddMonths(y);

                ErrorRepotedByClient temp = new ErrorRepotedByClient();
               
                 temp.Monthlydate = t.Year.ToString().Substring(2, 2) + " " + getMonthStr(t.Month - 1);

                list.Add(temp);

            }


            adoConn = dbconnection();

            adoCmd = new SqlCommand("spErrorReportedByClient", adoConn);
            adoCmd.Parameters.Add(new SqlParameter("@MonthStartDate", startDate));
            adoCmd.Parameters.Add(new SqlParameter("@MonthEndDate", endDate));
            adoCmd.CommandType = CommandType.StoredProcedure;

            try
            {
                adoConn.Open();

                prodResult = adoCmd.BeginExecuteReader();

                waitHandles[0] = prodResult.AsyncWaitHandle;
                WaitHandle.WaitAll(waitHandles);

                errData = adoCmd.EndExecuteReader(prodResult);


                while (errData.Read())
                {

                    DateTime t = errData.GetDateTime(errData.GetOrdinal("Dates"));
                    int d = Math.Abs(((stdate.Year - t.Year) * 12) + stdate.Month - t.Month);

                    ErrorRepotedByClient rp = list[d];
                    double err = errData.GetInt32(errData.GetOrdinal("NumOfClient"));
                    double errVal = (err / totalClient) * 100;
                    rp.ApprovedVal = (int)errVal;
                   
                }

                adoConn.Close();
            }
            catch (Exception ex)
            {


            }

            return list;
        }


        public static List<ComparativeDataProd> GetComparativeDataProd(string startDate, string endDate)
        {
            
            List<ComparativeDataProd> list = new List<ComparativeDataProd>();

            SqlConnection adoConn = new SqlConnection();
            SqlCommand adoCmd = null;
            SqlDataReader comData = null;

            IAsyncResult prodResult = null;
            WaitHandle[] waitHandles = new WaitHandle[1];

            string[] mons = { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" };

            DateTime stdate = Convert.ToDateTime(startDate);
            DateTime eddate = Convert.ToDateTime(endDate);
            int mondif = ((eddate.Year - stdate.Year) * 12) + eddate.Month - stdate.Month;

            adoConn = dbconnection();

            adoCmd = new SqlCommand("spComparativeDataProd", adoConn);
            adoCmd.Parameters.Add(new SqlParameter("@DateStart", startDate));
            adoCmd.Parameters.Add(new SqlParameter("@DateEnd", endDate));
            adoCmd.CommandType = CommandType.StoredProcedure;

            try
            {
                adoConn.Open();

                prodResult = adoCmd.BeginExecuteReader();

                waitHandles[0] = prodResult.AsyncWaitHandle;
                WaitHandle.WaitAll(waitHandles);

                comData = adoCmd.EndExecuteReader(prodResult);


                for (int x = 0; x <= mondif; x++)
                {
                    ComparativeDataProd temp = new ComparativeDataProd();

                    DateTime t = stdate.AddMonths(x);
                    temp.Monthly = getMonthStr(t.Month - 1);
                    temp.ProdLTSVal = 0;
                    temp.ProdTMSVal = 0;
                    list.Add(temp);

                }

                while (comData.Read())
                {
                    int app = comData.GetOrdinal("FkApplicationID");

                    if (!comData.IsDBNull(app))
                    {
                        int appID = comData.GetInt32(app);

                        switch (appID)
                        {

                            case 3:

                                int x = comData.GetOrdinal("ShipmentDate");

                                if (!comData.IsDBNull(x))
                                {
                                    
                                    
                                    DateTime s = comData.GetDateTime(x);

                                    int d = Math.Abs(((stdate.Year - s.Year) * 12) + stdate.Month - s.Month);
                                    ComparativeDataProd t = list[d];

                                    t.ProdTMSVal = s.Day;
                                }

                                break;

                            case 4:

                                    int y = comData.GetOrdinal("ShipmentDate");

                                    DateTime s2 = comData.GetDateTime(y);

                                    int d2 = Math.Abs(((stdate.Year - s2.Year) * 12) + stdate.Month - s2.Month);
                                    ComparativeDataProd t2 = list[d2];

                                    t2.Monthly = mons[s2.Month-1];
                                    t2.ProdLTSVal = s2.Day;

                                break;

                        }

                    }


                }
                   adoConn.Close();

            }
            catch (Exception ex)
            {


            }

            return list;
        }


        public static List<DailyCumulativeClient> GetDailyCumulativeClient(string startDate, string endDate, string currdate)
        {
            List<DailyCumulativeClient> list = new List<DailyCumulativeClient>();

            double totalTMSclient = 0;
            double totalLTSclient = 0;

            double activeclient = 0;
            SqlConnection adoConn = new SqlConnection();
            SqlCommand adoCmd = null;
            SqlDataReader dailyCmClient = null;

            IAsyncResult prodResult = null;
            WaitHandle[] waitHandles = new WaitHandle[1];

            adoConn = dbconnection();
            adoCmd = new SqlCommand("spDailyCumulativeClient", adoConn);
            adoCmd.Parameters.Add(new SqlParameter("@MonthStartDate", startDate));
            adoCmd.Parameters.Add(new SqlParameter("@MonthEndDate", endDate));
            adoCmd.Parameters.Add(new SqlParameter("@CurrDate", currdate));
            adoCmd.CommandType = CommandType.StoredProcedure;

            try
            {
                adoConn.Open();

                prodResult = adoCmd.BeginExecuteReader();

                waitHandles[0] = prodResult.AsyncWaitHandle;
                WaitHandle.WaitAll(waitHandles);

                dailyCmClient = adoCmd.EndExecuteReader(prodResult);

                // have to get percentage instead of total.
                //activeclient = getNumOfActiveClients();
                activeclient = 20;

                int y = int.Parse(currdate.Split('/')[1]);

               // int day = (8 < y) ? y : 8;

                // adding production days
                for (int x = 1; x <= 18; x++)
                {
                    DailyCumulativeClient p = new DailyCumulativeClient();
                    p.ProdDay = x;
                    p.target = 0;
                    list.Add(p);
                }

                while (dailyCmClient.Read())
                {
                    int app = dailyCmClient.GetOrdinal("FkApplicationID");

                   if (!dailyCmClient.IsDBNull(app))
                   {
                       int appID = dailyCmClient.GetInt32(app);

                      switch(appID){

                          case 3: 
                              
                               int x = dailyCmClient.GetOrdinal("ProdDay");

                               if (!dailyCmClient.IsDBNull(x))
                               {

                                   int pday = (int)dailyCmClient.GetInt32(x);

                                   if (pday<=5)
                                   {

                                       DailyCumulativeClient c1 = list[pday - 1];

                                       int c = (int)dailyCmClient.GetInt32(dailyCmClient.GetOrdinal("ReadyToProcess"));

                                       c1.NumofTMSClient = (c / activeclient) *100;

                                       totalTMSclient = totalTMSclient + c1.NumofTMSClient;
                                       c1.TotalTMSClient = totalTMSclient;
                                   }

                               }

                       break;


                          case 4:
                               int z = dailyCmClient.GetOrdinal("ProdDay");

                               if (!dailyCmClient.IsDBNull(z))
                               {

                                   int pday = (int)dailyCmClient.GetInt32(z);

                                   if (pday <= 5)
                                   {

                                       DailyCumulativeClient c2 = list[pday - 1];

                                       int c = (int)dailyCmClient.GetInt32(dailyCmClient.GetOrdinal("ReadyToProcess"));


                                       c2.NumofLTSClient = (c / activeclient) * 100;

                                       totalLTSclient = totalLTSclient + c2.NumofLTSClient;
                                       c2.TotalLTSClient = totalLTSclient;
                                   }

                               }
                       break;

                    }
                   


                   }


                }

                DailyCumulativeClient t = list[4];
                t.target = 90;
                adoConn.Close();

            }
            catch (Exception ex)
            {


            }

            return list;
        }


        public static List<DailyCumulativeRC> GetDailyCumulativeRC(string startDate, string endDate, string currdate)
        {
            List<DailyCumulativeRC> list = new List<DailyCumulativeRC>();
            int numofRC = 0;
            double cumulativeTMSRC =0;
            double totalTMSRC = 0;
            double cumulativeLTSRC = 0;
            double totalLTSRC = 0;
            int day=0;

            SqlConnection adoConn = new SqlConnection();
            SqlConnection adoClientConn = new SqlConnection();

            SqlCommand adoCmd = null;
           
            SqlDataReader dailyCmRC = null;
           
            IAsyncResult prodResult = null;
            WaitHandle[] waitHandles = new WaitHandle[1];

            adoConn = dbconnection();
            adoCmd = new SqlCommand("spDailyCumulativeRC", adoConn);
            adoCmd.Parameters.Add(new SqlParameter("@MonthStartDate", startDate));
            adoCmd.Parameters.Add(new SqlParameter("@MonthEndDate", endDate));
            adoCmd.Parameters.Add(new SqlParameter("@CurrDate", currdate));
            adoCmd.CommandType = CommandType.StoredProcedure;

            try
            {
                adoConn.Open();

                prodResult = adoCmd.BeginExecuteReader();

                waitHandles[0] = prodResult.AsyncWaitHandle;
                WaitHandle.WaitAll(waitHandles);

                dailyCmRC = adoCmd.EndExecuteReader(prodResult);

                int y = int.Parse(currdate.Split('/')[1]);

                day = (8 < y) ? y : 8;

                for (int x = 1; x <= day; x++)
                {
                    DailyCumulativeRC p = new DailyCumulativeRC();
                    p.ProdDay = x;
                    list.Add(p);
                }

                // get the client ID for the day
                while (dailyCmRC.Read())
                {
                    int app = dailyCmRC.GetOrdinal("FkApplicationID");

                    if (!dailyCmRC.IsDBNull(app))
                    {
                        int appID = dailyCmRC.GetInt32(app);

                        switch (appID)
                        {

                            case 3:

                                int x = dailyCmRC.GetOrdinal("ProdDay");

                                if (!dailyCmRC.IsDBNull(x))
                                {

                                    int pday = (int)dailyCmRC.GetInt32(x);
                                    DailyCumulativeRC temp1 = list[pday - 1];

                                    int cID = (int)dailyCmRC.GetInt32(dailyCmRC.GetOrdinal("ClientID"));

                                    // return num of RC for the client
                                    numofRC = getNumOfRCforClient(cID);

                                    temp1.NumofTMSRC = temp1.NumofTMSRC + numofRC;

                                    totalTMSRC = totalTMSRC + numofRC;
                                    numofRC = 0;

                                }

                                break;

                            case 4:

                                int z = dailyCmRC.GetOrdinal("ProdDay");

                                if (!dailyCmRC.IsDBNull(z))
                                {

                                    int pday = (int)dailyCmRC.GetInt32(z);
                                    DailyCumulativeRC temp2 = list[pday - 1];

                                    int cID = (int)dailyCmRC.GetInt32(dailyCmRC.GetOrdinal("ClientID"));

                                    // return num of RC for the client
                                    numofRC = getNumOfRCforClient(cID);
                                   // numofRC = 15;

                                    temp2.TotalLTSRC = temp2.TotalLTSRC + numofRC;
                                    totalLTSRC = totalLTSRC + numofRC;
                                    numofRC = 0;

                                }

                                break;
                        }

                    }


                }
                // add the cumulatives of RC
                for (int x = 0; x < day; x++)
                {
                    DailyCumulativeRC temp = list[x];
                    if(x<5){
                       
                        temp.NumofTMSRC = (temp.NumofTMSRC / totalTMSRC) *100;
                        cumulativeTMSRC = cumulativeTMSRC + temp.NumofTMSRC;
                        temp.TotalTMSRC = cumulativeTMSRC;

                        temp.NumofLTSRC = (temp.NumofLTSRC / totalLTSRC) *100;
                        cumulativeLTSRC = cumulativeLTSRC + temp.NumofLTSRC;
                        temp.TotalLTSRC = cumulativeLTSRC;

                    }else{

                        temp.NumofLTSRC = 0;
                        temp.NumofTMSRC = 0;

                    }

                }

                DailyCumulativeRC t = list[4];
                t.target = 90;


                adoConn.Close();
            }
            catch (Exception ex)
            {


            }



            return list;
        }

      
        public static List<HighBacklogByMonth> GetHighBacklogByMonth(string backlogYear)
        {

            List<HighBacklogByMonth> list = new List<HighBacklogByMonth>();
            int[] highbacklog = new int[12];
            int ready = 0;
            int ship = 0;
            string[] mons ={"Jan", "Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"};

            string startDate = "01/01/" + backlogYear;
            string endDate = "12/31/" + backlogYear;

            SqlConnection adoConn = new SqlConnection();
            SqlCommand adoCmd = null;
            SqlDataReader prodStaus = null;

            IAsyncResult prodResult = null;
            WaitHandle[] waitHandles = new WaitHandle[1];

            adoConn = dbconnection();

            adoCmd = new SqlCommand("spBacklogForYear", adoConn);
            adoCmd.Parameters.Add(new SqlParameter("@YearStartDate", startDate));
            adoCmd.Parameters.Add(new SqlParameter("@YearEndDate", endDate));
            adoCmd.CommandType = CommandType.StoredProcedure;


            try
            {
                adoConn.Open();

                prodResult = adoCmd.BeginExecuteReader();

                waitHandles[0] = prodResult.AsyncWaitHandle;
                WaitHandle.WaitAll(waitHandles);

                prodStaus = adoCmd.EndExecuteReader(prodResult);

                while (prodStaus.Read())
                {

                    int x = prodStaus.GetOrdinal("Months");
                    if (!prodStaus.IsDBNull(x))
                    {
                        int months = (int)prodStaus.GetInt32(x);

                        switch (months)
                        {
                            case 1:
                                int y1 = prodStaus.GetOrdinal("ProdDay");
                                if (!prodStaus.IsDBNull(y1))
                                {
                                        int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                        if (t == 1)
                                        {
                                            ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }
                                        if (t == 2)
                                        {
                                            ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }

                                     if ( highbacklog[0] < (ready - ship) )
                                     {
                                         highbacklog[0] = (ready - ship);
                                     }
                                     ready = ship = 0;
                                    
                                }

                                break;
                            case 2:
                                int y2 = prodStaus.GetOrdinal("ProdDay");
                                if (!prodStaus.IsDBNull(y2))
                                {
                                   
                                        int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                        if (t == 1)
                                        {
                                            ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }
                                        if (t == 2)
                                        {
                                            ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }

                                        if (highbacklog[1] < (ready - ship))
                                        {
                                            highbacklog[1] = (ready - ship);
                                        }
                                        ready = ship = 0;
                                    
                                }

                                break;

                            case 3:
                                int y3 = prodStaus.GetOrdinal("ProdDay");
                                if (!prodStaus.IsDBNull(y3))
                                {
                                   
                                        int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                        if (t == 1)
                                        {
                                            ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }
                                        if (t == 2)
                                        {
                                            ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }

                                        if (highbacklog[2] < (ready - ship))
                                        {
                                            highbacklog[2] = (ready - ship);
                                        }
                                        ready = ship = 0;
                                    
                                }

                                break;

                            case 4:

                                int y4 = prodStaus.GetOrdinal("ProdDay");
                                if (!prodStaus.IsDBNull(y4))
                                {
                                    
                                        int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                        if (t == 1)
                                        {
                                            ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }
                                        if (t == 2)
                                        {
                                            ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }

                                        if (highbacklog[3] < (ready - ship))
                                        {
                                            highbacklog[3] = (ready - ship);
                                        }
                                        ready = ship = 0;
                                    
                                }
                                break;

                            case 5:
                                int y5 = prodStaus.GetOrdinal("ProdDay");
                                if (!prodStaus.IsDBNull(y5))
                                {
                                    
                                        int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                        if (t == 1)
                                        {
                                            ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }
                                        if (t == 2)
                                        {
                                            ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }

                                        if (highbacklog[4] < (ready - ship))
                                        {
                                            highbacklog[4] = (ready - ship);
                                        }
                                        ready = ship = 0;
                                }

                                break;

                            case 6:
                                int y6 = prodStaus.GetOrdinal("ProdDay");
                                if (!prodStaus.IsDBNull(y6))
                                {
                                   
                                        int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                        if (t == 1)
                                        {
                                            ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }
                                        if (t == 2)
                                        {
                                            ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }

                                        if (highbacklog[5] < (ready - ship))
                                        {
                                            highbacklog[5] = (ready - ship);
                                        }
                                        ready = ship = 0;
                                    
                                }

                                break;

                            case 7:
                                int y7 = prodStaus.GetOrdinal("ProdDay");
                                if (!prodStaus.IsDBNull(y7))
                                {
                                    
                                        int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                        if (t == 1)
                                        {
                                            ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }
                                        if (t == 2)
                                        {
                                            ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }

                                        if (highbacklog[6] < (ready - ship))
                                        {
                                            highbacklog[6] = (ready - ship);
                                        }
                                        ready = ship = 0;
                                    
                                }

                                break;

                            case 8:
                                int y8 = prodStaus.GetOrdinal("ProdDay");
                                if (!prodStaus.IsDBNull(y8))
                                {
                                    
                                        int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                        if (t == 1)
                                        {
                                            ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }
                                        if (t == 2)
                                        {
                                            ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }

                                        if (highbacklog[7] < (ready - ship))
                                        {
                                            highbacklog[7] = (ready - ship);
                                        }
                                        ready = ship = 0;
                                    
                                }

                                break;

                            case 9:
                                int y9 = prodStaus.GetOrdinal("ProdDay");
                                if (!prodStaus.IsDBNull(y9))
                                {
                                    
                                        int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                        if (t == 1)
                                        {
                                            ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }
                                        if (t == 2)
                                        {
                                            ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }

                                        if (highbacklog[8] < (ready - ship))
                                        {
                                            highbacklog[8] = (ready - ship);
                                        }
                                        ready = ship = 0;
                                    
                                }

                                break;

                            case 10:

                                int y10 = prodStaus.GetOrdinal("ProdDay");
                                if (!prodStaus.IsDBNull(y10))
                                {
                                   
                                        int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                        if (t == 1)
                                        {
                                            ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }
                                        if (t == 2)
                                        {
                                            ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }

                                        if (highbacklog[9] < (ready - ship))
                                        {
                                            highbacklog[9] = (ready - ship);
                                        }
                                        ready = ship = 0;
                                   
                                }
                                break;

                            case 11:

                                int y11 = prodStaus.GetOrdinal("ProdDay");
                                if (!prodStaus.IsDBNull(y11))
                                {
                                   
                                        int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                        if (t == 1)
                                        {
                                            ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }
                                        if (t == 2)
                                        {
                                            ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }

                                        if (highbacklog[10] < (ready - ship))
                                        {
                                            highbacklog[10] = (ready - ship);
                                        }
                                        ready = ship = 0;
                                    
                                }
                                break;

                            case 12:
                                int y12 = prodStaus.GetOrdinal("ProdDay");
                                if (!prodStaus.IsDBNull(y12))
                                {
                                       int t = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Test"));

                                        if (t == 1)
                                        {
                                            ship = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }
                                        if (t == 2)
                                        {
                                            ready = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("Institute"));
                                        }

                                        if (highbacklog[11] < (ready - ship))
                                        {
                                            highbacklog[11] = (ready - ship);
                                        }
                                        ready = ship = 0;
                                }

                                break;
                        }

                    }


                }

                for (int z = 0; z < 12; z++)
                {

                    HighBacklogByMonth l = new HighBacklogByMonth();
                    l.Monthly = mons[z];
                    l.BacklogAmount= highbacklog[z];
                    list.Add(l);
                }


            }
            catch (Exception e)
            {

            }

            return list;
        }


        public static List<AvgClientTurnaround> GetAvgClientTurnaround(string startDate, string endDate)
        {
            List<AvgClientTurnaround> list = new List<AvgClientTurnaround>();
            List<AvgClientTurnaround> res = new List<AvgClientTurnaround>();

            SqlConnection adoConn = new SqlConnection();
            SqlCommand adoCmd = null;
            SqlDataReader prodStaus = null;

            IAsyncResult prodResult = null;
            WaitHandle[] waitHandles = new WaitHandle[1];

            adoConn = dbconnection();

            adoCmd = new SqlCommand("spProcessingTurnaround", adoConn);
            adoCmd.Parameters.Add(new SqlParameter("@MonthStartDate", startDate));
            adoCmd.Parameters.Add(new SqlParameter("@MonthEndDate", endDate));
            adoCmd.CommandType = CommandType.StoredProcedure;

            int len = Int32.Parse(endDate.Split('/')[1]);
            double turnaround = 0;
            double totClient = 0;

            for (int x = 0; x < len ; x++)
            {
                AvgClientTurnaround temp = new AvgClientTurnaround();
                temp.ProdDay = x;
                temp.NoOfClient = 0;
                temp.ClientTurnaround = 0;
                list.Add(temp);
                
            }

            try
            {
                adoConn.Open();

                prodResult = adoCmd.BeginExecuteReader();

                waitHandles[0] = prodResult.AsyncWaitHandle;
                WaitHandle.WaitAll(waitHandles);

                prodStaus = adoCmd.EndExecuteReader(prodResult);


                while (prodStaus.Read())
                {
                    int x = prodStaus.GetOrdinal("Shipped");
                    int y = prodStaus.GetOrdinal("ReadyProcess");

                    if (!prodStaus.IsDBNull(x))
                    {
                        if (!prodStaus.IsDBNull(y))
                        {

                            int ship = (int)prodStaus.GetInt32(x);
                            int ready = (int)prodStaus.GetInt32(y);

                            AvgClientTurnaround t = list[ship - ready];
                            t.NoOfClient++;
                        }
                    }


                }


                for (int x = 0; x < len; x++)
                {
                    AvgClientTurnaround temp = list[x];
                    turnaround = turnaround + temp.NoOfClient * x;
                    totClient = totClient + temp.NoOfClient;

                }

                AvgClientTurnaround fres = new AvgClientTurnaround();
                fres.ClientTurnaround = Math.Round(turnaround / totClient, 1); ;
                res.Add(fres);

            }
            catch (Exception ex)
            {

            }


            return res;


        }


        public static List<AvgRCTurnaround> GetAvgRCTurnaround(string startDate, string endDate)
        {

            List<AvgRCTurnaround> list = new List<AvgRCTurnaround>();
            List<AvgRCTurnaround> results = new List<AvgRCTurnaround>();

            SqlConnection adoConn = new SqlConnection();
            SqlCommand adoCmd = null;
            SqlDataReader rcturn = null;

            IAsyncResult prodResult = null;
            WaitHandle[] waitHandles = new WaitHandle[1];

            adoConn = dbconnection();

            adoCmd = new SqlCommand("spProcessingTurnaround", adoConn);
            adoCmd.Parameters.Add(new SqlParameter("@MonthStartDate", startDate));
            adoCmd.Parameters.Add(new SqlParameter("@MonthEndDate", endDate));
            adoCmd.CommandType = CommandType.StoredProcedure;

            int numofRCperClient = 0;

            int len = Int32.Parse(endDate.Split('/')[1]);
            double rcturnaround = 0;
            double totRC = 0;

            for (int x = 0; x < len; x++)
            {
                AvgRCTurnaround temp = new AvgRCTurnaround();
                temp.ProdDay = x;
                temp.NoOfRC = 0;
                list.Add(temp);

            }

            try
            {

                adoConn.Open();

                prodResult = adoCmd.BeginExecuteReader();

                waitHandles[0] = prodResult.AsyncWaitHandle;
                WaitHandle.WaitAll(waitHandles);

                rcturn = adoCmd.EndExecuteReader(prodResult);

                while (rcturn.Read())
                {

                    int ship = (int)rcturn.GetInt32(rcturn.GetOrdinal("Shipped"));
                    int ready = (int)rcturn.GetInt32(rcturn.GetOrdinal("ReadyProcess"));
                    int cID = (int)rcturn.GetInt32(rcturn.GetOrdinal("ClientID"));

                    numofRCperClient = getNumOfRCforClient(cID);

                    AvgRCTurnaround t = list[ship - ready];
                    t.NoOfRC = t.NoOfRC + numofRCperClient;

                }

                for (int x = 0; x < len; x++)
                {
                    AvgRCTurnaround temp = list[x];
                    rcturnaround = rcturnaround + temp.NoOfRC * x;
                    totRC = totRC + temp.NoOfRC;
                }

                AvgRCTurnaround fres = new AvgRCTurnaround();
                fres.RCTurnaround = Math.Round(rcturnaround / totRC , 1 );
                results.Add(fres);

            }
            catch(Exception ex)
            {


            } 

            return results;
        }


        public static List<ProductDayCompare> GetProductDayCompare(string startDate, string endDate, string currdate)
        {
            List<ProductDayCompare> list = new List<ProductDayCompare>();
            double shippedAmt = 0;
            double readyToPro = 0;
            double waitingdata = 0;
            double holdAndNotPro = 0;
            double backlog = 0;

            int activeClients = 0;
            // activeClients = getNumOfActiveClients();
            activeClients = 20;

            List<ProductionStatus> ps = GetProductionStatus(startDate, endDate, currdate);

            for (int y = 0; y < int.Parse(currdate.Split('/')[1]); y++)
            {
                ProductionStatus temp = ps[y];
                shippedAmt = shippedAmt + temp.ShippedCount;
                readyToPro = readyToPro + temp.ReadyToProTMS;
            }

            List<ProductionException> ex = GetProductionException(startDate, endDate, currdate);
            waitingdata = ex[0].ProcessTMSVal;
            holdAndNotPro = ex[1].ProcessTMSVal + ex[2].ProcessTMSVal;



            shippedAmt = (shippedAmt / activeClients) * 100;
            waitingdata = (waitingdata / activeClients) * 100;


            if ((readyToPro - shippedAmt) > 0)
            {
                backlog = ((readyToPro - shippedAmt) / activeClients) * 100;
            }
            else
            {

                backlog = 0.0;
            }


            holdAndNotPro = (holdAndNotPro / activeClients) * 100;

            ProductDayCompare c1 = new ProductDayCompare();
            c1.Descrip = "Shipped";
            c1.ProdValue = shippedAmt;
            list.Add(c1);

            ProductDayCompare c2 = new ProductDayCompare();
            c2.Descrip = "Waiting Data";
            c2.ProdValue = waitingdata;
            list.Add(c2);

            ProductDayCompare c3 = new ProductDayCompare();
            c3.Descrip = "Backlog";
            c3.ProdValue = backlog;
            list.Add(c3);

            ProductDayCompare c4 = new ProductDayCompare();
            c4.Descrip = "Hold or Not Processing";
            c4.ProdValue = holdAndNotPro;
            list.Add(c4);

            return list;
        }


        public static List<ProductionReport> GetProductionReport(string startDate, string endDate)
        {

            List<ProductionReport> list = new List<ProductionReport>();

            SqlConnection adoConn = new SqlConnection();
            SqlCommand adoCmd = null;
            SqlDataReader waitData = null;

            int totshipped = 0;

            string[] day = startDate.Split('/');
            int days = System.DateTime.DaysInMonth(int.Parse(day[2]), int.Parse(day[0]));

            List<ProductionStatus> ps = GetProductionStatus(startDate, endDate, endDate);


            for (int x = 1; x <= days; x++)
            {
                ProductionStatus t = ps[x - 1];

                ProductionReport temp = new ProductionReport();
                temp.Day = x;
                temp.Date = day[0] + "/" + x + "/" + day[2];
                temp.DayOfWeek = (int)Convert.ToDateTime(temp.Date).DayOfWeek;


                temp.Shipped = t.ShippedCount;

                totshipped = totshipped + t.ShippedCount;
                temp.TotalShipped = totshipped;

                temp.Backlog = t.ReadyToProTMS - t.ShippedCount;

                list.Add(temp);

            }

            string sqlHold = "SELECT CAST(DATEPART(DD,P.ProcessedDate) AS INT) AS ProdDay , count(P.HasErrors) AS OnHold " +
                             "FROM ProductionStatus2 AS P " +
                             "WHERE P.AreAllFilesUploaded =1 AND P.ProcessedDate BETWEEN " + startDate + " AND " + endDate + " AND P.HasErrors =1 AND P.FkApplicationID=3" +
                             "GROUP BY P.ProcessedDate";
            adoConn = dbconnection();
            adoCmd = adoConn.CreateCommand();
            adoCmd.CommandType = CommandType.Text;
            adoCmd.CommandText = sqlHold;
            try
            {
                adoConn.Open();

                waitData = adoCmd.ExecuteReader();

                while (waitData.Read())
                {

                    int s = (int)waitData.GetInt32(waitData.GetOrdinal("ProdDay"));
                    ProductionReport t = list[s - 1];
                    t.Onhold = (int)waitData.GetInt32(waitData.GetOrdinal("OnHold"));

                }

                adoConn.Close();

            }
            catch (Exception ex)
            {


            }

            return list;

        }


        /**
		*  return no of active client by connecting different client databases
		*/
        private static int getNumOfActiveClients()
        {
            int activeClient = 0;
            int clientUnitID = 0;
            SqlConnection adoConn = new SqlConnection();
            SqlConnection adoConnClient = new SqlConnection();
            SqlCommand adoCmd = null;
            SqlCommand adoCmdUnitID = null;
            SqlCommand adoCmdActive = null;
            SqlDataReader prodStaus = null;
            SqlDataReader clientUnitReader = null;
            SqlDataReader clientActiveReader = null;

            IAsyncResult prodResult = null;
            WaitHandle[] waitHandles = new WaitHandle[1];

            IAsyncResult getClientUnit = null;
            WaitHandle[] unitwaitHandle = new WaitHandle[1];

            IAsyncResult getClientActive = null;
            WaitHandle[] activewaitHandle = new WaitHandle[1];

            string CltSQL = "SELECT * FROM Client WHERE ClientID <> 0";

            adoConn.ConnectionString = "user ID=" + "sa" + ";data source=" + "192.168.15.157" + ";initial catalog=" + "FMSISystem" + ";password=" + "zone@123" + ";Asynchronous Processing=true;MultipleActiveResultSets=true;Connect Timeout=30;Connection Lifetime=60;Max Pool Size=1000";

            adoCmd = adoConn.CreateCommand();
            adoCmd.CommandText = CltSQL;
            adoCmd.CommandType = CommandType.Text;

            try
            {

                adoConn.Open();

                prodResult = adoCmd.BeginExecuteReader();

                waitHandles[0] = prodResult.AsyncWaitHandle;
                WaitHandle.WaitAll(waitHandles);

                prodStaus = adoCmd.EndExecuteReader(prodResult);


                while (prodStaus.Read())
                {
                    int clientID = (int)prodStaus.GetInt32(prodStaus.GetOrdinal("ClientID"));
                    string serverName = prodStaus.GetString(prodStaus.GetOrdinal("ServerName"));
                    string DbName = prodStaus.GetString(prodStaus.GetOrdinal("DBName"));
                    string username = prodStaus.GetString(prodStaus.GetOrdinal("Username"));
                    string password = prodStaus.GetString(prodStaus.GetOrdinal("Password"));


                    adoConnClient.ConnectionString = "user ID=" + username + ";data source=" + serverName + ";initial catalog=" + DbName + ";password=" + password + ";Asynchronous Processing=true;MultipleActiveResultSets=true;Connect Timeout=30;Connection Lifetime=60;Max Pool Size=1000";
                    adoCmdUnitID = new SqlCommand("spGetClientUnitId", adoConnClient);
                    adoCmdUnitID.CommandType = CommandType.StoredProcedure;
                    adoCmdUnitID.Parameters.Add(new SqlParameter("@clientId", clientID));

                    try
                    {

                        adoConnClient.Open();

                        getClientUnit = adoCmdUnitID.BeginExecuteReader();

                        waitHandles[0] = getClientUnit.AsyncWaitHandle;
                        WaitHandle.WaitAll(waitHandles);

                        clientUnitReader = adoCmdUnitID.EndExecuteReader(getClientUnit);

                        while (clientUnitReader.Read())
                        {
                            clientUnitID = clientUnitReader.GetInt32(clientUnitReader.GetOrdinal("UnitID"));
                        }

                        if (clientUnitID != 0)
                        {

                            string isActiveSQL = "SELECT ua.FkUnitID, ua.Value " +
                                                  "FROM UnitAttrib ua " +
                                                  "WHERE ua.FkUnitID = " + clientUnitID + " AND ua.FkAttribTypeID = 44 " +
                                                  "AND ua.BeginDate <= GETDATE() AND (ua.EndDate IS NULL OR ua.EndDate > GETDATE())";

                            adoCmdActive = adoConnClient.CreateCommand();
                            adoCmdActive.CommandText = isActiveSQL;
                            adoCmdActive.CommandType = CommandType.Text;

                            try
                            {

                                getClientActive = adoCmdActive.BeginExecuteReader();

                                waitHandles[0] = getClientActive.AsyncWaitHandle;
                                WaitHandle.WaitAll(waitHandles);

                                clientActiveReader = adoCmdActive.EndExecuteReader(getClientActive);

                                while (clientActiveReader.Read())
                                {
                                    string act = clientActiveReader.GetString(clientActiveReader.GetOrdinal("Value"));

                                    if (act == "1")
                                    {
                                        activeClient++;
                                    }
                                }

                            }
                            catch (Exception ex)
                            {


                            }

                        }

                    }
                    catch (Exception ex)
                    {


                    }
                    finally
                    {

                        if (adoConnClient != null)
                        {
                            adoConnClient.Close();
                        }

                    }


                }


            }
            catch (Exception ex)
            {

            }


            return activeClient;
        }

        // return num of RC for the client
        private static int getNumOfRCforClient(int clientID)
        {
            SqlConnection adoConn = new SqlConnection();
            SqlConnection adoClientConn = new SqlConnection();
            SqlCommand adoClientCmd = null;
            SqlCommand adoUnitAmount = null;
            SqlDataReader clientConnData = null;
            SqlDataReader UnitAmntData = null;

            IAsyncResult prodResult = null;
            WaitHandle[] waitHandles = new WaitHandle[1];

            int numofRC = 0;

            adoConn = dbconnection();

            string SQLCon = "SELECT * FROM Client WHERE ClientID=" + clientID;

            adoClientCmd = adoConn.CreateCommand();
            adoClientCmd.CommandText = SQLCon;
            adoClientCmd.CommandType = CommandType.Text;


            try
            {
                adoConn.Open();

                prodResult = adoClientCmd.BeginExecuteReader();

                waitHandles[0] = prodResult.AsyncWaitHandle;
                WaitHandle.WaitAll(waitHandles);

                clientConnData = adoClientCmd.EndExecuteReader(prodResult);

                while (clientConnData.Read())
                {
                    string serverName = clientConnData.GetString(clientConnData.GetOrdinal("ServerName"));
                    string DbName = clientConnData.GetString(clientConnData.GetOrdinal("DBName"));
                    string username = clientConnData.GetString(clientConnData.GetOrdinal("Username"));
                    string password = clientConnData.GetString(clientConnData.GetOrdinal("Password"));


                    string unitAmount = "select count(unitid) as Units from unit " +
                                        "where fkunittypeid = 50 and fkclientid =" + clientID;

                    adoClientConn.ConnectionString = "user ID=" + username + ";data source=" + serverName + ";initial catalog=" + DbName + ";password=" + password + ";Asynchronous Processing=true;MultipleActiveResultSets=true;Connect Timeout=30;Connection Lifetime=60;Max Pool Size=1000";
                    adoUnitAmount = adoClientConn.CreateCommand();
                    adoUnitAmount.CommandType = CommandType.Text;
                    adoUnitAmount.CommandText = unitAmount;


                    try
                    {
                        adoClientConn.Open();

                        UnitAmntData = adoUnitAmount.ExecuteReader();

                        while (UnitAmntData.Read())
                        {

                            numofRC = (int)UnitAmntData.GetInt32(UnitAmntData.GetOrdinal("Units"));

                        }


                    }
                    finally
                    {
                        if (adoClientConn != null)
                        {
                            adoClientConn.Close();
                        }
                    }

                }


            }
            catch (Exception ex)
            {


            }

            return numofRC;
        }


        private static SqlConnection dbconnection()
        {
            SqlConnection adoConn = new SqlConnection();
            adoConn.ConnectionString = "user ID=" + "sa" + ";data source=" + "192.168.15.157" + ";initial catalog=" + "FMSISystem" + ";password=" + "zone@123" + ";Asynchronous Processing=true;MultipleActiveResultSets=true;Connect Timeout=30;Connection Lifetime=60;Max Pool Size=1000";

            return adoConn;

        }


        private static string getMonthStr(int x)
        {
            string[] mons = { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" };
            return mons[x];


        }

    }

   
}